/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.CrecerConDeporteVO;
import com.informet.ipd.modelo.vo.ProgEstrVO;
import com.informet.ipd.modelo.vo.ProyectosInversionVO;
import com.informet.ipd.modelo.vo.RolUsuarioVO;
import com.informet.ipd.modelo.vo.UbigeoVO;
import com.informet.ipd.modelo.vo.UsuarioVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class CrecerConDeporteDao extends DBase{


  Connection conn = null;

    public CrecerConDeporteDao() {
        super();
    }

    public void  setOGE(ProgEstrVO bean, ResultSet rs) throws SQLException{
        bean.setCOD_OGE(rs.getInt("num_codobj_pk"));
        bean.setDESC_OGE(rs.getString("tx_descobj"));    
    }

     public void  setOEE(ProgEstrVO bean, ResultSet rs) throws SQLException{
       bean.setCOD_OEE(rs.getInt("num_codobjesp_pk"));
        bean.setDESC_OEE(rs.getString("tx_descobjesp"));
    }

    public void  setActividades(ProgEstrVO bean, ResultSet rs) throws SQLException{
       bean.setCOD_ACTIVIDAD(rs.getInt("num_codactper_pk"));
       bean.setDESC_ACTIVIDA(rs.getString("tx_descactper"));
    }

    public void  setANIOS(ProgEstrVO bean, ResultSet rs) throws SQLException{
       bean.setCOD_ANIO(rs.getInt("num_codanio_pk"));
       bean.setDESC_ANIO(rs.getString("tx_descanio"));
    }

    public void  setListTableTituProg(ProgEstrVO bean, ResultSet rs) throws SQLException{
           bean.setTituProgTab(rs.getString("tx_tituloprg"));
           bean.setJustiProgTab(rs.getString("tx_justiprg"));
           bean.setCodPrograma(rs.getInt("num_codprg_pk"));

           bean.setCOD_OGE(rs.getInt("num_codobj"));
           bean.setCOD_OEE(rs.getInt("num_codobjesp_pk"));
           bean.setCOD_ACTIVIDAD(rs.getInt("num_codactper_pk"));          
           bean.setFECHA_INICIO(rs.getString("dt_iniprg"));
           bean.setFECHA_FIN(rs.getString("dt_terprg"));
           bean.setRUTA(rs.getString("tx_rutaprg"));
           bean.setCOD_ANIO(rs.getInt("num_anioprg"));
           bean.setMONTO(rs.getBigDecimal("num_presuprg"));
          // bean.setCOD_ANIO(rs.getInt(url));
          // bean.setMONTO(rs.getInt("monto"));

    }

      public void  setListRegion(UbigeoVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODDPTO_PK(rs.getString("NUM_CODDPTO_PK"));
          bean.setTX_NOMDPTO(rs.getString("TX_NOMDPTO"));
          
    }



        public void  setComboProvincia(UbigeoVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODPRV_PK(rs.getString("NUM_CODPRV_PK"));
          bean.setTX_NOMPRV(rs.getString("TX_NOMPRV"));

    }
     public void  setComboDistrito(UbigeoVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODDIST_PK(rs.getString("NUM_CODDIST_PK"));
          bean.setTX_NOMDIST(rs.getString("TX_NOMDIST"));
      
    }

     public void  setComboUgel(UbigeoVO bean, ResultSet rs) throws SQLException{
          bean.setNUM_CODUGEL_PK(rs.getString("NUM_CODUGEL_PK"));
          bean.setTX_NOMBUGEL(rs.getString("TX_NOMBUGEL"));

    }

     public void  setDisciplina(CrecerConDeporteVO bean, ResultSet rs) throws SQLException{
          bean.setNumCodDisc(Integer.parseInt(rs.getString("NUM_CODDISC_PK")));
          bean.setTxsDescDisc(rs.getString("TX_DESCDISC"));

    }

    public void  setListMccd(CrecerConDeporteVO bean, ResultSet rs) throws SQLException{
          
          bean.setDireccion(rs.getString("TX_DIRECCCD"));
          bean.setProfesor(rs.getString("TX_PROFCCD"));
          bean.setSupervisor(rs.getString("TX_SUPERCCD"));
          bean.setTxsDescDisc(rs.getString("TX_descdisc"));
          bean.setInstitucionEducativa(rs.getString("TX_INSTCCD"));

          bean.setAnio(rs.getString("NUM_ANIOCCD"));
          bean.setNivel(rs.getString("NUM_NIVELCCD"));
          bean.setAnioInicio(rs.getString("NUM_ANIOINICCD"));
          bean.setUgel(rs.getString("NUM_CODUGEL_PK"));
          bean.setCodCcd(Integer.parseInt(rs.getString("NUM_CCD_PK")));          
          bean.setNumCodDisc(Integer.parseInt(rs.getString("NUM_CODDISC_PK")));
          //ubigeo
          bean.setCodRegion(rs.getString("num_coddpto_pk"));
          bean.setCodProvincia(rs.getString("num_codprv_pk"));
          bean.setCodDistrito(rs.getString("num_coddist_pk"));

          if(rs.getString("NUM_CONTINUIDAD").equals("0")){
                bean.setContinuidad("No");
                bean.setCodContinuidad("0");
          }
          
          else{

                bean.setContinuidad("Si");
                bean.setCodContinuidad("1");
          }
          
          
                  

    }



     public List getlistDisciplina(String idPerfil,String codDisc) throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<CrecerConDeporteVO> lista = new ArrayList<CrecerConDeporteVO>(0);
        //List lista=null;
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select * from me_disciplina where 1=1";
            if(idPerfil!=null)
            sql=sql+"  and num_codperfil_pk="+idPerfil;
            if(codDisc!=null)
            sql="  and num_coddisc_pk="+codDisc;

            rs = stmt.executeQuery(sql);
            CrecerConDeporteVO bean = null;
            while (rs.next()) {
                bean = new CrecerConDeporteVO();
               setDisciplina(bean, rs);

               lista.add(bean);

            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

           public List getRegion()  throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<UbigeoVO> lista = new ArrayList<UbigeoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            //sql = "select * from me_programa";
            //////
                 sql = "SELECT  NUM_CODDPTO_PK,TX_NOMDPTO FROM ME_DEPARTAMENTO";

            ////
            rs = stmt.executeQuery(sql);
            UbigeoVO bean = null;
            while (rs.next()) {
                bean = new UbigeoVO();
                setListRegion(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }

        return lista;
    }


      public List getComboProvincia(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<UbigeoVO> lista = new ArrayList<UbigeoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "SELECT  NUM_CODPRV_PK,TX_NOMPRV FROM ME_PROVINCIA WHERE NUM_CODDPTO_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            UbigeoVO bean = null;
            while (rs.next()) {
                bean = new UbigeoVO();
                setComboProvincia(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

public List getComboDistrito(String id) throws SQLException {

        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<UbigeoVO> lista = new ArrayList<UbigeoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "SELECT  NUM_CODDIST_PK,TX_NOMDIST FROM ME_DISTRITO WHERE NUM_CODPRV_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            UbigeoVO bean = null;
            while (rs.next()) {
                bean = new UbigeoVO();
                setComboDistrito(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
     //  JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }


    public List getComboUgel(String id) throws SQLException {

        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<UbigeoVO> lista = new ArrayList<UbigeoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);

            sql = "SELECT  * FROM ME_UGEL WHERE NUM_CODDIST_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            UbigeoVO bean = null;
            while (rs.next()) {
                bean = new UbigeoVO();
                setComboUgel(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }

public List getListMeCcd(CrecerConDeporteVO bean) throws SQLException {

        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<CrecerConDeporteVO> lista = new ArrayList<CrecerConDeporteVO>(0);
        //lista=null;
        String sql = "";
        
        try {
            conn = DriverManager.getConnection(url, usuario, password);            
            stmt = conn.createStatement();
            sql = " select "+
                  "  *  "+
                  "  from me_ccd c inner join me_disciplina d on c.num_coddisc_pk=d.num_coddisc_pk "+
		  "			   inner join me_ugel u on u.num_codugel_pk=c.num_codugel_pk "+

                  " inner join me_distrito di on di.num_coddist_pk=u.num_coddist_pk "+
                  " inner join me_provincia p on p.num_codprv_pk=di.num_codprv_pk "+
                  " inner join me_departamento de on de.num_coddpto_pk=p.num_coddpto_pk "+


                  "  where 1=1 ";
 //direccion,ugel,Anio,profesor,nivel,continuidad,institucionEducativa,supervisor,
//tipoOper,Integer.parseInt(codDisciplina),anioInicio,Integer.parseInt(codCcdHidden)
                  if((!bean.getUgel().equals("-1") ))
                  {
                    sql=sql+"     and u.num_codugel_pk="+bean.getUgel();
                  }
                  if((!bean.getAnio().equals("-1") ))
                    sql=sql+"     and c.num_anioccd="+bean.getAnio();

                  if((!bean.getAnioInicio().equals("-1") ))
                    sql=sql+"     and c.num_anioiniccd="+bean.getAnioInicio();

                  if((bean.getNumCodDisc()!=-1 ))
                    sql=sql+"     and d.num_coddisc_pk ="+bean.getNumCodDisc();

                  if((!bean.getNivel().equals("-1") ))
                        sql=sql+"     and c.num_nivelccd="+bean.getNivel();

                 //Ini agregado para realizar busqueda completa
                   if((!bean.getContinuidad().equals("-1") ))
                        sql=sql+"     and c.num_continuidad="+bean.getContinuidad();

                   if((!bean.getInstitucionEducativa().equals("") ))
                        sql=sql+"     and c.tx_instccd like '%"+bean.getInstitucionEducativa()+"%'";

                   if((!bean.getSupervisor().equals("") ))
                        sql=sql+"     and c.tx_superccd like '%"+bean.getSupervisor()+"%'";
            
                   if((!bean.getProfesor().equals("") ))
                        sql=sql+"     and c.tx_profccd like '%"+bean.getProfesor()+"%'";

                 //Fin agregado para realizar busqueda completa
            

            rs = stmt.executeQuery(sql);
            CrecerConDeporteVO bean1 = null;
            while (rs.next()) {
                bean1 = new CrecerConDeporteVO();
                setListMccd(bean1, rs);
                lista.add(bean1);
            }
           
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }
       

      public String spMan(CrecerConDeporteVO bean) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {
        	
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_MeCcd(?,?,?,?,?,?,?,?,?,?,?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto
            cs.setString(1, bean.getDireccion());
            cs.setString(2, bean.getUgel());
            cs.setString(3, bean.getAnio());
            cs.setString(4, bean.getProfesor());
            cs.setString(5, bean.getNivel());
            cs.setString(6, bean.getContinuidad());
            cs.setString(7, bean.getInstitucionEducativa());
            cs.setString(8, bean.getSupervisor());
            cs.setString(9, bean.getTipoOper());
            cs.setInt(10, bean.getNumCodDisc());
            cs.setString(11, bean.getAnioInicio());
            cs.setInt(12, bean.getCodCcd());
            
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

}
